package com.ruoyi.utils;

import com.ruoyi.linxi.annotation.ExcelMapping;
import com.ruoyi.linxi.entry.Invoice;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Title TODO
 * @Description TODO
 * @Author CAI ZHAI
 * @Date 2021/9/17 22:34
 **/
@Slf4j
public class ExcelUtil{
    //获取单元格
    public static String getStringValueFromCell(Cell cell) {
        SimpleDateFormat sFormat = new SimpleDateFormat("MM/dd/yyyy");
        DecimalFormat decimalFormat = new DecimalFormat("#.#");
        String cellValue = "";
        if(cell == null) {
            return cellValue;
        }else if(cell.getCellType() == CellType.STRING) {
            cellValue = cell.getStringCellValue();
        }else if(cell.getCellType() == CellType.NUMERIC) {
            if(HSSFDateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(d);
                cellValue = sFormat.format(date);
            }else {
                //cellValue = decimalFormat.format((cell.getNumericCellValue()));
                cellValue = Double.toString(cell.getNumericCellValue());
            }
        }else if(cell.getCellType() == CellType.BLANK) {
            cellValue = "";
        }else if(cell.getCellType() == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType() == CellType.ERROR) {
            cellValue = "";
        }else if(cell.getCellType() == CellType.FORMULA) {
            cellValue = cell.getCellFormula().toString();
        }
        return cellValue;
    }
    
    //sheet转obj
    public static <T> List<T>  sheetToObj(String fileName, int sheetIndex, Class<T> clazz){
        List<T> resList = new ArrayList<>();
        
        try{
            InputStream is = new FileInputStream(new File(fileName));
            Workbook hssfWorkbook = null;
            if (fileName.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(is);// Excel 2007zz
            } else if (fileName.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(is);// Excel 2003
            
            }
            // 循环工作表Sheet
            // HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            Sheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
            if(hssfSheet==null){
                log.error("sheet==null");
                return null;
            }
    
            // 循环行Row
            List<Map<String,String>> rowList = new ArrayList<>();
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                Row titleRow = hssfSheet.getRow(0);
    
                Row hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    Map<String,String> map = new HashMap<>();
                    short lastCellNum = titleRow.getLastCellNum();
                    for(int i = 0; i<lastCellNum; i++){
                        Cell cell = titleRow.getCell(i);
                        String titleString = cell.getStringCellValue();
                        String value = getStringValueFromCell(hssfRow.getCell(i));
                        map.put(titleString.trim(), value);
                    }
                    rowList.add(map);
                }
            }
    
            // List中的数据就是在Excel中读取的内容
            Field[] declaredFields = clazz.getDeclaredFields();
            for(Map<String,String> rowMap : rowList){
                T t = clazz.newInstance();
                for(Field field : declaredFields){
                    ExcelMapping annotation = field.getAnnotation(ExcelMapping.class);
                    String value = rowMap.get(annotation.title());
                    ReflectUtil.setFiledValue(field, value, t);
                }
                resList.add(t);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    
        return resList;
    }
    
    public static void test1(double dou) {
        String dou_str = Double.toString(dou);
        System.out.println(dou_str);
    }
    
    public static void main(String[] args) {
        double dou = 12.35;
        test1(dou);
    }
    
    //根据列号获取值
    public static String getStringValue(Row row, int i){
        if(row!=null){
            Cell cell = row.getCell(i);
            if(cell!=null){
               return getStringValueFromCell(cell);
            }
        }
        
        return "";
    }
}
